The Excel Import Template Document

The excel import template consists of different sheets:

Retail items: For the items that will be imported.
Retail groups: For retail groups that will be imported.
Retail department: For retail departments that will be imported.
Customers: For customers that will be imported.
Vendors: For vendors that will be imported.
_LookupValues: This is a sheet for the lookup data in other sheets. Do not change anything on this sheet.

Once a template has been fetched, the template can be populated with data either by manually entering data or by copying and pasting columns of data from other systems.

The Excel template has a few simple rules:

The column’s top line has the field name as it is in the data table. Each name has a color that specifies if the field is required or optional.
Red columns are required to always contain data; an example would be ITEMID on Retail Items.
Blue columns are only used when importing variant items.
Orange columns are required to contain data when inserting new records, while it is optional when updating records.
Black columns are not required to contain data.

Some of the fields in the Excel document have a drop-down box where you can choose from valid enumerations for the given field. Other fields are filtered on maximum counts of letters or on numeric data types.

All fields of the Excel document

Retail items worksheet

To import items you need to create one new line per item to be created.

To edit items through excel change the settings of your exported items in this sheet and import again.

Fields & buttons	Description
ITEMID	Alphanumerical characters are accepted. If the ID exists in the database that item will be updated depending on your import method selection in the Import Excel document dialog
VARIANT HEADER ID	Enter the ID of the header item. The header item will be created if it does not exist.
DESCRIPTION	Enter the description of the item. This description is used on the receipts, item search and reports. When importing an item for the first time that is upon item creation this field must be filled in.
VARIANT DESCRIPTION	Enter the description of the variant item. Variant items created in LS One usually include the names of the dimension attributes that describes them.
DIMENSIONS/ATTRIBUTES	Dimensions and attributes can be created for variants in the import. Choose a separator character to use between dimensions/attributes. For Header item line enter the name of the dimensions to be impoted with the separation symbol between. For an example: Color;Size;Style For the Variants enter the name of the attributes this variant has and in the same order as the dimensions were added for the header item. For an example: Black;XL;Long
SEARCH ALIAS	An additional name for the item which is also searched through in the item search. Information is inserted into Retail > Retail items > Retail item view > Additional information tab > Search alias
ITEM TYPE	Specifies the type of item. Valid values are found in the lookup values in the import template. These are: Retail (default value if nothing entered) Service Header Assembly Variant items are set based on the Variant Header ID column and can therefore leave this column blank.
NOTES	An additional text field to keep information about the item. Information is inserted into Retail > Retail items > Retail item view > Additional information tab > Extended description
INVENTORY UNIT	Enter the ID of the unit the item is ordered and stored in. This can be other than the sales unit but then make sure there exists a unit conversion between the two units. Information is inserted into Retail > Retail items > Retail item view > Inventory tab > Inventory unit You can find the ID of the units in General setup > Units. Right click the unit line and select the Copy ID option and paste it into the Excel worksheet.
SALES UNIT	Enter the ID of the unit the item is sold in. This can be other than the inventory unit but then make sure there exists a unit conversion between the two units. Information is inserted into Retail > Retail items > Retail item view > Inventory tab > Inventory unit You can find the ID of the units in General setup > Units. Right click the unit line and select the Copy ID option and paste it into the Excel worksheet.
RETAIL GROUP	Enter the ID of the retail group the item should be added to. If you are creating new item groups also use the ID you are using for the new group in the Retail group worksheet. If the retail group already exists you can find the ID of the retail groups in Retail> Retail items > Retail groups. Right click the retail group line and select the Copy ID option and paste it into the Excel worksheet.
SALES TAX GROUP	Enter the ID of the item sales tax group the item should be in. You can find a list of all sales tax groups and their ID's in General setup > Sales tax > Item sales tax group.
COST PRICE	Enter the cost price of the item. The cost price is inserted into the field found at Retail item view > Prices tab > Cost price.
SALES PRICE	Enter the cost price of the item. The cost price is inserted into the field found at Retail item view > Prices tab > Cost price. This price is used when the item is sold on the POS. If no valid promotions or trade agreement sales prices are found.
BARCODESETUP	If you are importing bar codes for the item enter the ID of the barcode setup to be used. This field can be left empty even though you are importing bar codes.
BARCODE	Enter or scan in (if you have a keyboard scanner) the barcode to sell the item with.
BARCODEUNIT	Enter the ID of the Unit the item is sold in when the barcode is scanned.
SCALE ITEM	Enter 0 if the item is NOT a scale item. Enter 1 if the item IS a scale item and should be weighed when sold. If the field is left empty the default is that the item is not a scale item.
KEY IN PRICE	Select from the drop down list how keying in price for an item is handled on the POS. If this field is left empty the default is that keying in price is Not mandatory
KEY IN QUANTITY	Select from the drop down list how keying in quantity for an item is handled on the POS. If this field is left empty the default is that keying in price is Not mandatory
ZERO PRICE VALID	Enter 0 if zero price is NOT valid for the item . Enter 1 if zero price IS valid for the item. If the field is left empty the default is that zero price is NOT valid.
NO DISCOUNT ALLOWED	Enter 0 if manual discounts can be given to this item. Enter 1 if NO manual discounts can be given to this item. If the field is left empty the default is that discounts are allowed.
MUST KEY IN COMMENT	Enter 0 if a comment is not necessary. Enter 1 if the comment dialog should pop up every time this item is sold. If the field is left empty the comments will not be necessary.
QTY BECOMES NEGATIVE	Enter 0 to sell the item in positive quantity. That is the item is subtracted from your inventory. Enter 1 to sell the item in negative quantity. The item is added to your inventory every time you sell it similar to when you return an item. If the field is left empty the item will be sold in positive quantity and subtracted from your inventory.
MUST SELECT UNIT	Enter 0 if a unit selection is not necessary. Enter 1 if the unit of measure selection dialog should pop up every time this item is sold. If the field is left empty the unit selection will not be necessary.
VENDOR ITEMID	Enter the ID your vendor uses for this product.
VENDORID	Enter the ID of the vendor you purchase this product from. If the vendor already exists in your data you can find the Vendor ID here: General setup > Inventory > View all vendors. If this is a new vendor you can import the vendor by filling in the Vendor. This vendor will become the default vendor for the item when the item is imported.
VENDOR PRICE	The price you buy the item for from the vendor. This value will be imported as default purchase price for this item and vendor. This value will also be imported as last purchase price if the last purchase price has not been set yet.
KEY IN SERIAL NUMBER	Select from the drop down list how keying in quantity for an item is handled on the POS. If this field is left empty the default is that keying in price is Not mandatory

Retail groups worksheet

To import retail groups you need to create one new line per group to be created.

Fields & buttons	Description
ID	Alphanumerical characters are accepted. If the ID exists in the database that retail group will be updated depending on your import method selection in the Import Excel document dialog.
DESCRIPTION	Enter the description of the retail group. This description is used on the receipts, item search and reports. When importing an retail group for the first time that is upon item creation this field must be filled in.
RETAIL DEPARTMENT ID	Enter the ID of the department the retail group should be included in. This is not mandatory. If you are creating new item departments also use the ID you are using for the new department in the retail department worksheet. If the retail group already exists you can find the ID of the retail groups in Retail> Retail items > Retail groups. Right click the retail group line and select the Copy ID option and paste it into the Excel worksheet.
SALES TAX GROUP	Enter the ID of the item sales tax group the items in the retail group should be in. All new items created in this retail group will automatically be set to this item sales tax group (but can be changed individually) You can find a list of all sales tax groups and their ID's in General setup > Sales tax > Item sales tax group.

Retail department worksheet

To import retail departments you need to create one new line per department to be created.

Fields & buttons	Description
ID	Alphanumerical characters are accepted. If the ID exists in the database that retail department will be updated depending on your import method selection in the Import Excel document dialog.
DESCRIPTION	Enter the description of the retail departments. This description is used on the receipts, item search and reports. When importing an retail department for the first time that is upon item creation this field must be filled in.

Customers worksheet

To import customer you need to create one new line per customer to be created.

Fields & buttons	Description
ID	Alphanumerical characters are accepted. If the ID exists in the database that customer will be updated depending on your import method selection in the Import Excel document dialog.
NAME	Enter the name of the customer. This name is used on the receipts, item search and reports. When importing a customer for the first time that is upon item creation this field must be filled in. If the whole name is entered into this field the name will be inserted to the Display name field in the Customer view. But if you also fill in one or more of the following fields, middlename, lastname, nameprefix, namesuffix then this NAME field serves as the first name field.
MIDDLENAME	Enter the middle name of the customer. This name is used on the receipts, item search and reports. You can also leave this field empty.
LASTNAME	Enter the last name of the customer. This name is used on the receipts, item search and reports. You can also leave this field empty.
NAMEPREFIX	Use this field to add a name prefix such as Mr. or Mrs. to the customer name. You can also leave this field empty.
NAMESUFFIX	Use this field to add a name suffix such as Jr. to the customer name. You can also leave this field empty.
ADDRESSLINE1	Enter the default address of the customer. There are two lines to have enough space for a long address.
ADDRESSLINE2	Enter the default address of the customer. There are two lines to have enough space for a long address.
CITY	Enter the city of the customer address.
STATE	Enter the state of the customer address.
ZIP	Enter the zip of the customer address.
COUNTRY	Enter the country of the customer address.
ADDRESS FORMAT	Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in:
SEARCH ALIAS	Enter a search alias for the customer. This could be used for a ID number
PHONE	Enter the customer's phone number.
MOBILE PHONE	Enter the customer's mobile phone number.
EMAIL ADDRESS	Enter the customer's email address
LANGUAGE CODE	Enter the customer's language code. This is for informational use only.
RECEIPT EMAIL	Enter the email address the customer wants to receive the receipt to. A third party system is needed to actually send out the receipts.
RECEIPT OPTION	Select how the customer wants to receive the receipt. not set Print on POS Send in email Both
ACCOUNT NUMBER	If another customer should be invoiced for this customers credit then enter the ID of the paying customer.
IDENTIFICATION NUMBER	This field can be used to keep an identification number of the customer.
CURRENCY	The default currency for this customer.
VAT NUMBER	Enter the customer's VAT number if any.
SALES TAX GROUP	Enter the ID of the Customer sales tax group this customer should belong to. If nothing is selected the store sales tax group is used for tax calculations. This also depends on the tax calculation settings set on the Store.
MAXIMUM WITHDRAWAL	Enter the maximum credit limit this customer should have. If this customer is allowed to charge to his account then you have to enter
BLOCKING	Select one of the following option: Customer is not blocked: Select this option if you do not want to block the customer. Customer is limited to invoices: Select this option to limit the customer to invoices. Customer is blocked: Select this option to block the customer.
CASH CUSTOMER	Enter 0 if a comment is not necessary. Enter 1 if the comment dialog should pop up every time this item is sold. If the field is left empty the comments will not be necessary.

Vendor worksheet

To import vendors you need to create one new line per vendor to be created.

Fields & buttons	Description
VENDORID	Enter an ID for the Vendor. Alphanumerical characters are accepted. If the ID exists in the database that vendor will be updated depending on your import method selection in the Import Excel document dialog.
DESCRIPTION	Enter the name of the vendor. This name is used on the receipts, item search and reports. When importing a customer for the first time that is upon item creation this field must be filled in.
ADDRESSLINE1	Enter the default address of the customer. There are two lines to have enough space for a long address.
ADDRESSLINE2	Enter the default address of the customer. There are two lines to have enough space for a long address.
CITY	Enter the city of the customer address.
STATE	Enter the state of the customer address.
ZIP	Enter the zip of the customer address.
COUNTRY	Enter the country of the customer address.
ADDRESS FORMAT	Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in:
CURRENCY	Enter the ID of the currency used when doing business with this vendor.
PHONE	Enter the phone number of the vendor.
EMAIL ADDRESS	Enter the vendor's email address.
FAX	Enter the vendor's fax number.
LANGUAGE CODE	Enter the vendors's language code. This is for informational use only.
NOTES	Enter any notes you might have for this vendor.
TAX GROUP	Enter the ID of the sales tax code used for this vendor.
CONTACT TYPE	Select which type of contact the following information is for, a company or a person.
CONTACT COMPANYNAME	Enter the name of the whole name of the company here. IF this is a person you can either enter the whole name here or skip this field and fill the other name fields.
CONTACT NAMEPREFIX	Use this field to add a name prefix such as Mr. or Mrs. to the contact name. You can also leave this field empty.
CONTACT FIRSTNAME	Enter the first name of the contact.
CONTACT MIDDLENAME	Enter the middle name of the contact.
CONTACT LASTNAME	Enter the last name of the contact.
CONTACT NAMESUFFIX	Use this field to add a name suffix such as Jr. to the contact name.
CONTACT ADDRESSLINE1	Enter the default address of the contact. There are two lines to have enough space for a long address.
CONTACT ADDRESSLINE2	Enter the default address of the contact. There are two lines to have enough space for a long address.
CONTACT CITY	Enter the city of the contact address.
CONTACT STATE	Enter the state of the contact address.
CONTACT ZIP	Enter the zip of the contact address.
CONTACT COUNTRY	Enter the country of the contact address.
CONTACT ADDRESS FORMAT	Select of which address format this address is. If nothing is selected the default set in the Site Manager will be used. see further info in:
CONTACT PHONE	Enter a phone number for the vendor contact.
CONTACT PHONE2	Enter another phone number for the vendor contact.
CONTACT FAX	Enter a fax number for the vendor tasks.

Last updated:	April 2021
Version:	LS One 2021